/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Carrera;
import com.javabeans.Gestion;
import com.javabeans.Pensum;
/**
 *
 * @author Administrador
 */
public class AdminPensum implements PensumManager{
    private Connection c;

    public AdminPensum(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdPensum() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDPENSUM)+1,1) AS ID FROM PENSUM";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminPais -> getIdPensum() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void savePensum(Pensum p) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query="INSERT INTO PENSUM VALUES("+p.getIdPensum();
            query+= ", "+p.isVigente();
            query+= ", "+p.getNumMaterias()+","+p.getIdCarrera();
            query+= ","+p.getIdGestionIni()+","+p.getIdGestionFin()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminPensum savePensum(Pais p) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    public Pensum getPensumVigente(int idCarrera) throws DataException{
        Pensum p = new Pensum();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT P.IDPENSUM, P.IDCARRERA, P.VIGENTE, P.NUM_MATERIAS";
            query += ", P.IDGESTION_INI, P.IDGESTION_FIN";
            query += ", C.NOMBRE, C.SIGLA, C.IDFACULTAD";
            query += ", GI.NOMBRE NOMGESTION_INI, GI.YEAR YEARGESTION_INI";
            query += ", IFNULL(GF.NOMBRE, 'INDEFINIDO') NOMGESTION_FIN, IFNULL(GF.YEAR, 3000) YEARGESTION_FIN";
            query += " FROM PENSUM P";
            query += " INNER JOIN CARRERA C ON P.IDCARRERA=C.IDCARRERA";
            query += " INNER JOIN GESTION GI ON P.IDGESTION_INI=GI.IDGESTION";
            query += " LEFT JOIN GESTION GF ON P.IDGESTION_FIN=GF.IDGESTION";
            query += " WHERE P.IDCARRERA="+idCarrera+" AND P.VIGENTE=1";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                p.setIdPensum(rs.getInt("P.IDPENSUM"));
                p.setVigente(rs.getBoolean("P.VIGENTE"));
                p.setNumMaterias(rs.getInt("P.NUM_MATERIAS"));
                p.setIdCarrera(rs.getInt("P.IDCARRERA"));
                p.setIdGestionIni(rs.getInt("P.IDGESTION_INI"));
                p.setIdGestionFin(rs.getInt("P.IDGESTION_FIN"));
                
                Carrera ca = new Carrera();
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setIdFacultad(rs.getInt("C.IDFACULTAD"));
                p.setCarrera(ca);
                
                Gestion gi = new Gestion();
                gi.setNombre(rs.getString("NOMGESTION_INI"));
                gi.setYear(rs.getInt("YEARGESTION_INI"));
                
                Gestion gf = new Gestion();
                gf.setNombre(rs.getString("NOMGESTION_FIN"));
                gf.setYear(rs.getInt("YEARGESTION_FIN"));
                p.setGestionIni(gi);
                p.setGestionFin(gf);
            }
        }catch(SQLException e){
            throw new DataException(" AdminPensum -> getPensumVigente(int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return p;
    }
    
    @Override
    public Pensum getPensumVigenteBasic(int idCarrera) throws DataException{
        Pensum p = new Pensum();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPENSUM";
            query += ", NUM_MATERIAS";
            query += ", IDGESTION_INI";
            query += ", IDGESTION_FIN";
            query += " FROM PENSUM";
            query += " WHERE IDCARRERA="+idCarrera+" AND VIGENTE=1";             
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                p.setIdPensum(rs.getInt("IDPENSUM"));
                p.setVigente(true);
                p.setNumMaterias(rs.getInt("NUM_MATERIAS"));
                p.setIdCarrera(idCarrera);
                p.setIdGestionIni(rs.getInt("IDGESTION_INI"));
                p.setIdGestionFin(rs.getInt("IDGESTION_FIN"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminPensum -> getPensumVigenteBasic(int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return p;
    }
    
    @Override
    public List<Pensum> getPensumsInactivos(int idCarrera) throws DataException{
        List<Pensum> pensums = new ArrayList<Pensum>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT P.IDPENSUM, P.IDCARRERA, P.VIGENTE";
            query += ", P.NUM_MATERIAS, P.IDGESTION_INI, P.IDGESTION_FIN";
            query += ", GI.NOMBRE NOMGESTION_INI";
            query += ", DATE_FORMAT(GI.FECHAINI,'%d/%m/%Y') GIFECHAINI";
            query += ", IFNULL(GF.NOMBRE, 'INDEFINIDO') NOMGESTION_FIN";
            query += ", IFNULL(DATE_FORMAT(GF.FECHAINI,'%d/%m/%Y'), 'INDEFINIDO') GFFECHAFIN";
            query += " FROM PENSUM P";
            query += " INNER JOIN GESTION GI ON P.IDGESTION_INI=GI.IDGESTION";
            query += " LEFT JOIN GESTION GF ON P.IDGESTION_FIN=GF.IDGESTION";
            query += " WHERE P.IDCARRERA="+idCarrera+" AND P.VIGENTE=0";
            query += " ORDER BY GI.YEAR";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Pensum p = new Pensum();
                p.setIdPensum(rs.getInt("P.IDPENSUM"));
                p.setVigente(rs.getBoolean("P.VIGENTE"));
                p.setNumMaterias(rs.getInt("P.NUM_MATERIAS"));
                p.setIdCarrera(rs.getInt("P.IDCARRERA"));
                p.setIdGestionIni(rs.getInt("P.IDGESTION_INI"));
                p.setIdGestionFin(rs.getInt("P.IDGESTION_FIN"));
                
                Gestion gi = new Gestion();
                gi.setNombre(rs.getString("NOMGESTION_INI"));
                gi.setFechaIni(rs.getString("GIFECHAINI"));
                
                Gestion gf = new Gestion();
                gf.setNombre(rs.getString("NOMGESTION_FIN"));
                gf.setFechaFin(rs.getString("GFFECHAFIN"));
                p.setGestionIni(gi);
                p.setGestionFin(gf);
                pensums.add(p);
            }
        }catch(SQLException e){
            throw new DataException(" AdminPensum -> getPensumsPorCarrera(int idFacultad, int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return pensums;
    }
    
    public void updatePensum(Pensum p) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE PENSUM SET NUM_MATERIAS = "+p.getNumMaterias();
            query+= ", IDGESTION_INI = "+p.getIdGestionIni();
            query+= ", IDGESTION_FIN = "+p.getIdGestionFin();
            query+= " WHERE IDPENSUM="+p.getIdPensum();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminPensum -> updatePensum(Pensum p) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darBajaPensum(int idPensum) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE PENSUM SET VIGENTE = 0 WHERE IDPENSUM="+idPensum;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminPensum -> darBajaPensum(int idPensum) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public int getIdPensumVigente(int idCarrera, int idMateria) throws DataException{
        int idPensum = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT MP.IDPENSUM";
            query += " FROM MATERIA_PENSUM MP";
            query += " INNER JOIN PENSUM P ON MP.IDPENSUM=P.IDPENSUM AND P.VIGENTE=1";
            query += " WHERE MP.IDCARRERA="+idCarrera+" AND MP.IDMATERIA="+idMateria+" AND MP.ACTIVO=1";
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                idPensum = rs.getInt("MP.IDPENSUM");
        }catch(SQLException ex){
            throw new DataException(" AdminMateriaPensum -> getIdPensumVigente(int idPensum, int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
        return idPensum;
    }
}
